package com.bfg.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

@SuppressWarnings("serial")
public class QuestionRequestServlet2 extends HttpServlet {
	private Connection conn;
	private PreparedStatement prestmt;
	// properties for connecting to database

//	String url = "jdbc:sqlserver://bizring.biz:14333;databaseName=biztest;";
//	String username = "bizdev";
//	String password = "sql-2008-!@#$";

	String url = "jdbc:sqlserver://bizring.biz:14333;databaseName=bizring;";
	String username = "sa";
	String password = "qlalf@sql";

	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
	}

	@Override
	protected void service(HttpServletRequest req, HttpServletResponse res)
			throws ServletException, IOException {
		res.setContentType("text/html");
		PrintWriter out = res.getWriter();

		HttpSession session = req.getSession();
		
		session.removeAttribute("entry1Error");
//		session.setAttribute("entry1Error", "y");
		session.removeAttribute("entry2Error");
//		session.setAttribute("entry2Error", "y");
		session.removeAttribute("entry3Error");
//		session.setAttribute("entry3Error", "y");
		
		String action = req.getParameter("action");
		String pageRequested="./customer/question.html";
		conn = null;
		prestmt = null;
		if (action.equals("insert1")) {
			System.out.println("in1");
			String inPhoneNumber = validatePhoneNumber(req
					.getParameter("txtTitle1"));
			System.out.println(inPhoneNumber);
			try {
				// loading driver
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				// create connection
				conn = DriverManager.getConnection(url, username, password);
				// create query
				prestmt = conn
						.prepareStatement("INSERT INTO SMS_SEND_QUEUE(division, msisdn, contents, replyto, status, input_date) VALUES (?,?,?,?,?,getdate())");
				prestmt.setString(1, "W");
				prestmt.setString(2, inPhoneNumber);
				prestmt.setString(3, "$$+$$");
				prestmt.setString(4, "628998333833");
				prestmt.setString(5, "0");

				if (prestmt.executeUpdate() == 1) {
					prestmt.close();
					// out.println("data has been inserted");
					 session.setAttribute("entry1Error", "n");
//					 goToPage("/customer/customer_requested.jsp", req, res);
					res.sendRedirect(pageRequested);
				}

			} catch (SQLException e) {
//				throw new ServletException("Servlet could not insert a record",
//						e);
				e.printStackTrace();
				session.setAttribute("entry1Error", "y");
				res.sendRedirect(pageRequested);				
			} catch (ClassNotFoundException e) {
//				throw new ServletException("JDBC Driver not found", e);
				e.printStackTrace();
				session.setAttribute("entry1Error", "y");
				res.sendRedirect(pageRequested);
			} finally {
				// close connection if exist
				try {
					if (prestmt != null) {
						prestmt.close();
						prestmt = null;
					}
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
				}
			}
		}
		if (action.equals("insert2")) {
			System.out.println("in2");
			String inPhoneNumber = validatePhoneNumber(req
					.getParameter("txtTitle2"));
			System.out.println(inPhoneNumber);
			try {
				// loading driver
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				// create connection
				conn = DriverManager.getConnection(url, username, password);
				// create query
				prestmt = conn
						.prepareStatement("INSERT INTO SMS_SEND_QUEUE(division, msisdn, contents, replyto, status, input_date) VALUES (?,?,?,?,?,getdate())");
				prestmt.setString(1, "W");
				prestmt.setString(2, inPhoneNumber);
				prestmt.setString(3, "$$-$$");
				prestmt.setString(4, "628998333833");
				prestmt.setString(5, "0");

				if (prestmt.executeUpdate() == 1) {
					prestmt.close();
					// out.println("data has been inserted");
					session.setAttribute("entry2Error", "n");
//					goToPage("/customer/customer_requested.html", req, res);
					res.sendRedirect(pageRequested);
				}

			} catch (SQLException e) {
//				throw new ServletException("Servlet could not insert a record",
//						e);
				e.printStackTrace();
				session.setAttribute("entry2Error", "y");
				res.sendRedirect(pageRequested);

			} catch (ClassNotFoundException e) {
//				throw new ServletException("JDBC Driver not found", e);
				e.printStackTrace();
				session.setAttribute("entry2Error", "y");
				res.sendRedirect(pageRequested);
			} finally {
				// close connection if exist
				try {
					if (prestmt != null) {
						prestmt.close();
						prestmt = null;
					}
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
				}
			}

		}
		if (action.equals("sendInfo")) {
			System.out.println("sendInfo");
			String inEmail = req.getParameter("txtEmail");
			System.out.println(inEmail);
			String inPhone = req.getParameter("txtTitle3");
			System.out.println(inPhone);
			String inInfo = req.getParameter("txtInfo");
			System.out.println(inInfo);
			boolean boolEmail=sendEmail(inEmail, inPhone, inInfo);
			if(boolEmail==true){
				session.setAttribute("entry3Error", "n");
			}else{
				session.setAttribute("entry3Error", "y");
			}
			res
					.sendRedirect(pageRequested);
		}
		if (action.equals("select")) {
			out.println("<html>");
		    out.println("<head><title>Servlet JDBC</title></head>");
		    out.println("<body>");
		    out.println("<h1>Servlet JDBC</h1>");
			Statement stmt=null;
		    ResultSet rs = null;
			try {
				// loading driver
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				// create connection
				conn = DriverManager.getConnection(url, username, password);
				// create query
				stmt = conn.createStatement();
				rs=stmt.executeQuery("SELECT * FROM SMS_SEND_QUEUE");
				while(rs.next()){
					out.print(rs.getString("division"));
					out.print("\t\t\t");
					out.print(rs.getString("msisdn"));
					out.print("\t\t\t");
					out.print(rs.getString("contents"));
					out.print("\t\t\t");
					out.print(rs.getString("replyto"));
					out.print("\t\t\t");
					out.print(rs.getString("status"));
					out.print("\t\t\t");
					out.print(rs.getObject("timestamp").toString());
					out.print("<br>");
				}

			} catch (SQLException e) {
//				throw new ServletException("Servlet could not insert a record",
//						e);
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
//				throw new ServletException("JDBC Driver not found", e);
				e.printStackTrace();
			} finally {
				// close connection if exist
				try {
					if (prestmt != null) {
						prestmt.close();
						prestmt = null;
					}
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
				}
			}
		}
	}

	@Override
	public void destroy() {
		super.destroy();
	}

	// sending request to different page
	private void goToPage(String page, HttpServletRequest req,
			HttpServletResponse resp) throws ServletException, IOException {
		RequestDispatcher rd = req.getRequestDispatcher(page);
		if (rd != null)
			rd.forward(req, resp);

	}

	private String validatePhoneNumber(String strPhoneNumber) {
		String strCode = "";
		if (strPhoneNumber.charAt(0) == '0') {
			System.out.println("0");
			strCode = "62";
			strPhoneNumber = strPhoneNumber.substring(1, strPhoneNumber
					.length());
		}
		System.out.println(strPhoneNumber);
		return strCode + strPhoneNumber;

	}

	private boolean sendEmail(String inEmail, String inPhone, String txtInfo) {
		
		String strRecipients = "info@kamikayakreasi.com";

		Properties props = System.getProperties();
		props.put("mail.smtp.host", "smtp.gmail.com");
		props.put("mail.from", inEmail);
		props.put("mail.smtp.auth", "true");
		props.put("mail.smtp.port", "465");
//		props.put("mail.smtp.starttls.enable", "true");
		props.put("mail.smtp.socketFactory.port", "465");
		props.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
		props.put("mail.smtp.socketFactory.fallback", "false");
		Session session = Session.getDefaultInstance(props,
				new javax.mail.Authenticator() {

					protected PasswordAuthentication getPasswordAuthentication() {
						return new PasswordAuthentication(
								"info@kamikayakreasi.com", "info@bizring");
					}
				});
		session.setDebug(true);
		try {
			MimeMessage msg = new MimeMessage(session);
			InternetAddress addressFrom = new InternetAddress(inEmail);
			msg.setFrom(addressFrom);
			msg.setRecipients(Message.RecipientType.TO, strRecipients);	//info@kamikayakreasi.com
			msg.setSubject("[Info Biz-Ring] "+ inPhone);
			msg.setSentDate(new java.util.Date());
			msg.setContent("From : " + inEmail + "\r\n\r\n\r\n" + txtInfo, "text/plain");
			Transport.send(msg);
			System.out.println("Email has been sent.");
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Error in sending. Please try again.");
			return false;
		}
	}

}
